﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DAO
{
    public class CT_BCDTTheoLoaiPhongDAO
    {
        public class SqlConnectionData
        {
            public static SqlConnection Connection()
            {
                SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=QLKSAN;Integrated Security=True");
                return conn;
            }
        }

        //public static void InserCT_BCDTTheoLoaiPhong(CT_BCDTTheoLoaiPhongDTO ctdt)
        //{
        //    SqlConnection conn = SqlConnectionData.Connection();
        //    SqlCommand cmd = new SqlCommand("sp_InsertCT_BCDTTheoLoaiPhong", conn);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.Add("@MaBaoCaoDoanhThu", SqlDbType.Int);
        //    cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.Int);
        //    cmd.Parameters.Add("@DoanhThu", SqlDbType.Float);
        //    cmd.Parameters.Add("@TyLe", SqlDbType.Float);
        //    cmd.Parameters["@MaBaoCaoDoanhThu"].Value = ctdt.MaBaoCaoDoanhThu;
        //    cmd.Parameters["@MaLoaiPhong"].Value = ctdt.MaLoaiPhong;
        //    cmd.Parameters["@DoanhThu"].Value = ctdt.DoanhThu;
        //    cmd.Parameters["@TyLe"].Value = ctdt.TyLe;
        //    conn.Open();
        //    cmd.ExecuteNonQuery();
        //    conn.Close();
        //}
        public static void LayChiTietBaoCaoDoanhThuTheoLoaiPhong(int[] cacMaBaoCaoDoanhThu, ListView lv)
        {
            for (int i = 0; i < cacMaBaoCaoDoanhThu.Length; i++)
            {
                string sql = "Select lp.TenLoaiPhong, ct.DoanhThu, ct.TyLe From CT_BCDTTHEOLOAIPHONG ct, LOAIPHONG lp Where ct.MaLoaiPhong=lp.MaLoaiPhong and ct.MaBaoCaoDoanhThu=" + cacMaBaoCaoDoanhThu[i];
                DataTable dt = DataProvider.ExecuteQuery(sql);
                int stt = 1;
                foreach (DataRow dr in dt.Rows)
                {
                    ListViewItem lvit = new ListViewItem(stt.ToString());
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j == dt.Columns.Count - 1)
                        {
                            lvit.SubItems.Add(dr[j].ToString() + " %");
                            break;
                        }
                        lvit.SubItems.Add(dr[j].ToString());
                    }
                    lv.Items.Add(lvit);
                    stt++;
                }
            }
        }

        public static DataTable GetAll()
        {
            string sql = "Select * From CT_BCDTTHEOLOAIPHONG";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }

        public static void InserCT_BCDTTheoLoaiPhong(CT_BCDTTheoLoaiPhongDTO ctdt)
        {
            string sql = string.Format("INSERT INTO CT_BCDTTHEOLOAIPHONG(MaBaoCaoDoanhThu, MaLoaiPhong, DoanhThu, TyLe) VALUES (" + ctdt.MaBaoCaoDoanhThu + ", " + ctdt.MaLoaiPhong + ", " + ctdt.DoanhThu + ", " + ctdt.TyLe + ")");
            DataProvider.ExecuteNonQuery(sql);
        }
    }
}
